package orest.back.stat;

import java.sql.Connection;

import net.ssta.core.base.SQLBuilderBase;
import net.ssta.core.web.RequestKit;



/**
 * 
 * @ClassName:
 * @Description: 
 * @author: 
 * @date: 
 * 
 */
public class StatSql extends SQLBuilderBase
{
    /*
     * m_builder : 单例实例
     */
    private static StatSql m_builder = new StatSql();
    
    /**
     * @Title: getSQLBuilder
     * @Description: 获取实例
     * 
     * @param conn 数据库连
     * @return 单例实例
     */
    public static StatSql getSQLBuilder(Connection conn)
    {
        return m_builder;
    }
    
    /**
     * 科技成果数量统计
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return 
     */
    public static String kjcgSlStat(String startyear , String endyear)
    {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT kjcg_item_year KJCG_YEAR ,COUNT(T.KJCGID) TOTAL FROM T_KJCG T WHERE AchiveType like '1%' ");
        
        sb.append(" AND kjcg_item_year >= '").append(startyear).append("'");
        sb.append("  AND  kjcg_item_year <= '").append(endyear).append("'");
        
        
        
        sb.append(" GROUP BY kjcg_item_year ");
        sb.append(" ORDER BY kjcg_item_year ");
        return sb.toString();
    }
    
    
    /**
     * 按课题来源统计
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String keTiLaiYuan(String startyear , String endyear)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT DM.XXMC,Kjcg_Item_Year KJCG_YEAR,COUNT(T.KJCGID) TOTAL FROM T_KJCG T ");
        sb.append(" LEFT JOIN  (SELECT * FROM ZDXX Z WHERE Z.ZDDM='ITEM_126' ) DM  ");
        sb.append(" ON T.KJCG_ITEM_126 = DM.XXDM ");
        sb.append(" WHERE T.KJCG_ITEM_YEAR IS NOT NULL AND DM.XXMC IS NOT NULL ");
        sb.append(" AND Kjcg_Item_Year >= '").append(startyear).append("'");
        sb.append("  AND  Kjcg_Item_Year <= '").append(endyear).append("'");
        sb.append("  AND AchiveType like '1%' ");
        sb.append(" GROUP BY Kjcg_Item_Year,DM.XXMC,DM.SORT ");
        sb.append(" ORDER BY KJCG_YEAR,DM.SORT ");
        return sb.toString();
    }
    
    
    /**
     * 按成果水平统计
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String ShueiPing(String startyear , String endyear)
    {
    	StringBuffer sb = new StringBuffer();
        sb.append(" SELECT DM.XXMC,Kjcg_Item_Year KJCG_YEAR,COUNT(T.KJCGID) TOTAL FROM T_KJCG T ");
        sb.append(" LEFT JOIN  (SELECT * FROM ZDXX Z WHERE Z.ZDDM='ITEM_2' ) DM  ");
        sb.append(" ON T.KJCG_ITEM_2 = DM.XXDM ");
        sb.append(" WHERE T.KJCG_ITEM_YEAR IS NOT NULL AND DM.XXMC IS NOT NULL ");
        sb.append(" AND  Kjcg_Item_Year >= '").append(startyear).append("'");
        sb.append("  AND  Kjcg_Item_Year <= '").append(endyear).append("'");
        sb.append("  AND AchiveType like '1%' ");
        sb.append(" GROUP BY Kjcg_Item_Year,DM.XXMC,DM.SORT ");
        sb.append(" ORDER BY KJCG_YEAR,DM.SORT ");
        return sb.toString();
    }
    
    
    
    /**
     * 按研究形式统计
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String yanjiuXingShi(String startyear , String endyear)
    {
    	StringBuffer sb = new StringBuffer();
        sb.append(" SELECT DM.XXMC,Kjcg_Item_Year KJCG_YEAR,COUNT(T.KJCGID) TOTAL FROM T_KJCG T ");
        sb.append(" LEFT JOIN  (SELECT * FROM ZDXX Z WHERE Z.ZDDM='ITEM_201' ) DM  ");
        sb.append(" ON T.KJCG_ITEM_201 = DM.XXDM ");
        sb.append(" WHERE T.KJCG_ITEM_YEAR IS NOT NULL AND DM.XXMC IS NOT NULL ");
        sb.append(" AND Kjcg_Item_Year>= '").append(startyear).append("'");
        sb.append("  AND  Kjcg_Item_Year<= '").append(endyear).append("'");
        sb.append("  AND AchiveType like '1%' ");
        sb.append(" GROUP BY Kjcg_Item_Year,DM.XXMC,DM.SORT ");
        sb.append(" ORDER BY KJCG_YEAR,DM.SORT ");
        return sb.toString();
    }
    
    
    
    
    /**
     * 按体现形式统计
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String tiXianXingshiShi(String startyear , String endyear)
    {
    	StringBuffer sb = new StringBuffer();
        sb.append(" SELECT DM.XXMC,Kjcg_Item_Year KJCG_YEAR,COUNT(T.KJCGID) TOTAL FROM T_KJCG T ");
        sb.append(" LEFT JOIN  (SELECT * FROM ZDXX Z WHERE Z.ZDDM='ITEM_123' ) DM  ");
        sb.append(" ON T.KJCG_ITEM_123 = DM.XXDM ");
        sb.append(" WHERE T.KJCG_ITEM_YEAR IS NOT NULL AND DM.XXMC IS NOT NULL ");
        sb.append(" AND Kjcg_Item_Year>= '").append(startyear).append("'");
        sb.append("  AND  Kjcg_Item_Year<= '").append(endyear).append("'");
        sb.append("  AND AchiveType like '1%' ");
        sb.append(" GROUP BY Kjcg_Item_Year,DM.XXMC,DM.SORT ");
        sb.append(" ORDER BY KJCG_YEAR,DM.SORT ");
        return sb.toString();
    } 
    
    /**
     * 按行业统计
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String hangYe(String startyear , String endyear)
    {
    	StringBuffer sb = new StringBuffer();
        sb.append(" SELECT DM.XXMC,Kjcg_Item_Year KJCG_YEAR,COUNT(T.KJCGID) TOTAL FROM T_KJCG T  ");
        sb.append(" LEFT JOIN ( ");
        sb.append(" SELECT V.OPTIONVALUE XXDM,V.OPTIONNAME XXMC  FROM T_FIELDOPTION V ");
        sb.append(" WHERE V.TABLENAME='common' AND V.FIELDNAME='NewEnergyClass' AND NOT V.OPTIONVALUE LIKE '%.%' ) DM ");
        sb.append(" ON (T.NEWENERGYCLASS1 = DM.XXDM) ");
        sb.append(" WHERE  T.ACHIVETYPE LIKE '1%' AND  T.KJCG_ITEM_YEAR IS NOT NULL ");
        sb.append(" AND DM.XXMC IS NOT NULL ");
        sb.append(" AND Kjcg_Item_Year >= '").append(startyear).append("' ");
        sb.append(" AND  Kjcg_Item_Year <= '").append(endyear).append("' ");
        sb.append(" GROUP BY Kjcg_Item_Year,DM.XXMC ");
        sb.append(" ORDER BY KJCG_YEAR ");
              
        return sb.toString();
    }  
    
    
    
    
    /**
     * 获得行业分类
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String getHyTypes()
    {
    	StringBuffer sb = new StringBuffer();
        sb.append(" SELECT T.OPTIONVALUE XXDM ,T.OPTIONNAME XXMC FROM T_FIELDOPTION T ");
        sb.append(" WHERE T.TABLENAME='common' ");
        sb.append(" AND T.FIELDNAME='NewEnergyClass' ");
        sb.append(" AND NOT T.OPTIONVALUE LIKE '%.%' ");
        sb.append(" ORDER BY T.OPTIONVALUE");
        return sb.toString();
    }
    
    
    
    /**
     * 单位成果数量TOP10
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String unitAchieveTop10(String startyear , String endyear,String newenergyclass1)
    {
    	StringBuffer sb = new StringBuffer();
        
        sb.append(" SELECT DM.XXMC,COUNT(T.KJCGID) TOTAL FROM T_KJCG T ");
        sb.append(" LEFT JOIN (SELECT UNITID XXDM,ITEM_9 XXMC FROM T_Unit) DM ");
        sb.append(" ON T.UNITID = DM.XXDM ");
        sb.append(" WHERE T.KJCG_ITEM_YEAR IS NOT NULL ");
        sb.append(" AND DM.XXMC IS NOT NULL ");
        if(!startyear.equals("")){
        	sb.append(" AND KJCG_ITEM_YEAR>= '").append(startyear).append("'");
        }
        if(!endyear.equals("")){
        	sb.append(" AND  KJCG_ITEM_YEAR<= '").append(endyear).append("'");
        }
        
        if(!newenergyclass1.equals("")){
        	sb.append(" AND  NEWENERGYCLASS1 = '").append(newenergyclass1).append("'");
        }
        
        sb.append(" AND ACHIVETYPE like '1%' ");
        
        sb.append(" GROUP BY DM.XXMC,DM.XXDM ");
        sb.append(" ORDER BY TOTAL DESC");
        return sb.toString();
        
    } 
    
    
    
}
